import sys

import psycopg2


class DbOperate:
    def __init__(self, **dbinfo):
        self.db_info = dbinfo
        self.conn = None
        self.cur = None

    def conn_db(self):
        self.conn = psycopg2.connect(database=self.db_info['db_name'],
                                     user=self.db_info['db_user'],
                                     password=self.db_info['db_password'],
                                     host=self.db_info['db_host'],
                                     port=self.db_info['port'])
        self.conn.set_client_encoding('utf-8')
        self.cur = self.conn.cursor()
        print("connect successful")

    def exec_dml_sql(self, sql_cmd):
        print(sql_cmd)
        self.cur.execute(sql_cmd)
        self.conn.commit()
        print("dml operate successful")

    def exec_dql_sql(self, sql_cmd):
        self.cur.execute(sql_cmd)
        rows = self.cur.fetchall()
        print("select successful")
        return rows

    def close_conn(self):
        self.conn.close()
        print("close connect successful")


if __name__ == '__main__':
    Usage = f"Usage:input as 'python3 {sys.argv[0]} file_path "
    if len(sys.argv) < 1:
        print(Usage)
        exit(0)

    file_path = sys.argv[1]

    db_info = {'db_name': 'autotest_report',
               'db_user': 'u_report',
               'db_password': 'sua@222*w',
               'db_host': 'xx.xx.xx.xx',
               'port': 50517}
    my_db = DbOperate(**db_info)
    my_db.conn_db()
    # noinspection PyBroadException
    try:
        sql = 'select c.model_name, c.tc_name, ' \
              'case when c.run_result=\'ok\' then \'passed\' ' \
              'else \'failed\' end as run_result, c.run_time, ' \
              'c.core_num, c.end_time from (' \
              'select a.model_name, a.tc_name, a.run_result, ' \
              'cast(cast(substring(a.run_time from \'(.*)[smhd]\') ' \
              'as number) as integer) as run_time, b.core_num, ' \
              'substring(b.start_time+b.run_time, 0, 20) as end_time ' \
              'from "ReportApp_tier2funcinfo" a, ' \
              '"ReportApp_tier2funcmodel" b ' \
              'where a.run_os=b.run_os and a.model_name=b.model_name ' \
              'and a.ip_name=b.ip_name and a.run_os=\'x86_CentOS\' ' \
              'order by b.model_name) as c'
        sql_res = my_db.exec_dql_sql(sql)
        if sql_res:
            with open(file_path, 'w+', encoding='utf-8', errors='ignore') as f:
                for row in sql_res:
                    f.write(','.join([str(i) for i in row]) + '\n')
    except Exception as e:
        print(str(e))
    finally:
        my_db.close_conn()
